package account.dao.mysql;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.sql.DataSource;

import account.service.ServiceLocator;

public class ModelHelper<T> {


	public ArrayList< T > setModel( ArrayList< T > models, String tableName )  {
		
		ServiceLocator serviceLocator = new ServiceLocator();
		DataSource ds = serviceLocator.getDataSource();
		
		Connection conn = null;
		Statement st = null;
		ResultSet rset = null;
		
		try {
			conn = ds.getConnection();
			st = conn.createStatement();
			st.executeQuery("use account");
			
			for( T model : models ) {
				//1. odczytujemy pole typu id
				ArrayList< String > fieldsName = new ArrayList< String >();
				Integer id = null;
				String idFieldName = null;
				for (Field field : model.getClass().getDeclaredFields() ){
					if( field.getName().toLowerCase().startsWith("id_") && id == null) {
						Method method = null;
						Object result = null;
						try {
							idFieldName = field.getName();
							method = model.getClass().getMethod("get" + toFirstUp(idFieldName) );
							result = method.invoke(model);
						} catch( NoSuchMethodException e ) {
							throw new RuntimeException("Brakuje metody: Integer " + "get" + toFirstUp(field.getName()) + "()");
						} catch (IllegalArgumentException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}

						if( result instanceof Integer ) {
							id = (Integer) result;
						} else {
							throw new RuntimeException("Brakuje metody: Integer " + method.getName() + "()");
						}
					}
					else {
						fieldsName.add(field.getName() );
					}
				}
				
				if( id == -1 ) {
					String sql = "insert into " + tableName + " (";
						for( String fieldName : fieldsName) {
							sql += fieldName + ",";				
						}
						sql = trimLastComma( sql );
						sql += ") values ( ";
						
						for( String fieldName : fieldsName) {
							Method method = null;
							try {
								method = model.getClass().getMethod("get" + toFirstUp(fieldName) );	
								Object obj = method.invoke(model);
								sql += toSQLTypeString( obj ) + ",";							
							} catch (  NoSuchMethodException e  ) {} catch (IllegalArgumentException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (IllegalAccessException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (InvocationTargetException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
						}
						sql = trimLastComma( sql );
						sql += ")";
						st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
						rset = st.getGeneratedKeys();
						if( !rset.next() ) throw new RuntimeException("Brak klucza po insercie sql: " + sql);
						
						/*
						Method method = null;
						try {
							method = model.getClass().getMethod("set" + toFirstUp(idFieldName), Integer.class);
							method.invoke( model, rset.getObject( 1 ));
						} catch( NoSuchMethodException e ) {
							throw new RuntimeException("Brakuje metody: Integer " + "set" + toFirstUp(idFieldName) + "(" + rset.getObject( idFieldName ).getClass() + ")");
						} catch (IllegalArgumentException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						*/
						
				} else {
					String sql = "update " + tableName + " set ";
					for( String fieldName : fieldsName) {
						Method method = null;
						try {
							method = model.getClass().getMethod("get" + toFirstUp(fieldName) );
							Object obj = method.invoke(model);
							sql += fieldName + "=";					
							sql += toSQLTypeString( obj ) + ",";		
						} catch (SecurityException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (NoSuchMethodException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IllegalArgumentException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (IllegalAccessException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} catch (InvocationTargetException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}	
					}

					sql = trimLastComma( sql );
					sql += " where " + idFieldName + "=" + id;
					st.executeUpdate(sql);
				}
								
			}
			
			//st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			//rset = st.getGeneratedKeys();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
		if (rset != null)
			try {
				rset.close();
				if (st != null)
					st.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("!!!!!DUPA DUPIASTA insert");

				e.printStackTrace();
			}

	}
		
		return 	models;
	}
	
	public ArrayList<T> getModel(String sql, Class genericClass) {

		ArrayList<T> result = new ArrayList<T>();
		
		ServiceLocator serviceLocator = new ServiceLocator();
		DataSource ds = serviceLocator.getDataSource();

		Connection conn = null;
		Statement st = null;
		ResultSet rset = null;

		try {
			conn = ds.getConnection();
			st = conn.createStatement();
			st.executeQuery("use account");
			rset = st.executeQuery(sql);
			
			while (rset.next()) {
				T model = null;
				for (Field field : genericClass.getDeclaredFields()) {

					try {
						int pos = rset.findColumn(field.getName());
						String methodName = "set" + toFirstUp( field.getName() );
						//String methodName = "set" + field.getName().substring(0,1).toUpperCase() +  field.getName().substring(1);
						Method method = genericClass.getMethod(
								methodName, field.getType());
						if( model == null ) model = (T) genericClass.newInstance();
						method.invoke(model, rset.getObject(pos));
					} catch (SQLException e) {

					} catch (SecurityException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (NoSuchMethodException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (InstantiationException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}

				}
				if (model != null)
					result.add( model);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rset != null)
				try {
					rset.close();
					if (st != null)
						st.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					System.out.println("!!!!!DUPA DUPIASTA");

					e.printStackTrace();
				}

		}

		return result;
	}
	
	private String toFirstUp( String txt) {
		return txt.substring(0,1).toUpperCase() +  txt.substring(1);
	}
	
	private String toSQLTypeString( Object obj ) {
			if( obj instanceof String ) return "'" + obj.toString() + "'";
			if( obj instanceof Integer ) return obj.toString();
			if( obj instanceof Date) {
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
				return "'" + df.format(obj) + "'";
			}
			throw new RuntimeException("Nieznany typ: " + obj.getClass().getCanonicalName() );
		}
		
	

	private String trimLastComma( String txt) {
		if( txt.endsWith(",") ) return txt.substring( 0,  txt.length() - 1);
		return txt;
	}
	

}
